var express = require('express');
const db = require('../model/index');
const myroute = express.Router()
const mySql = require('../model/index')

// 1、用户角色配置
// 增加
myroute.post('/systemrole/add',(req,res)=>{
  // console.log(req.body.r_name);
  // console.log(req.body.r_date);
  const sql = `INSERT INTO roles VALUES(null,'${req.body.r_name}','个人中心','${req.body.r_depart}','${req.body.r_state}','${req.body.r_operator}','${req.body.r_date}','2000-01-01');`;

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,msg:'新增角色成功',data:data})
    }
  })
})
// 删除
myroute.post('/systemrole/del',(req,res)=>{
  const sql = `DELETE FROM roles WHERE r_id='${req.body.r_id}';`;
  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
        res.send({error:0,msg:'删除成功',data:data})
    }
  })
})
// 编辑/修改
myroute.post('/systemrole/edit',(req,res)=>{
  // const time = req.body.r_date.slice(0,10)
  // console.log(req.body.r_state);
  // console.log(time);
  const sql = `UPDATE roles SET r_state='${req.body.r_state}',r_date='${req.body.r_date}' WHERE r_id='${req.body.r_id}';`

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
        res.send({error:0,msg:'修改成功',data:data})
    }
  })
})
// 查询
myroute.get('/systemrole/get',(req,res)=>{
  // const page = req.query.page;
  // const pageSize = req.query.pageSize;

  const sql1 =`SELECT * FROM roles 
  LEFT JOIN department ON roles.r_depart= department.id
  WHERE r_name LIKE '%${req.query.name}%' and r_state like '${req.query.state}%' order by r_id;`;
  
  const sql2=`select count(*) as total from roles LEFT JOIN department ON roles.r_depart= department.id
  WHERE r_name LIKE '%${req.query.name}%' and r_state like '${req.query.state}%';`;
  mySql.query(sql1,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      mySql.query(sql2,(err2,data2)=>{
        if(err2){
          console.log(err2);
          res.send({error:1,msg:err2})
        }else{
          res.send({error:0,msg:'查询角色配置成功',data:data,total:data2[0].total})
        }
      })
    }
  })
})


// 2、权限配置
// 编辑/修改
myroute.post('/systempermission/edit',(req,res)=>{
  // console.log(req.body.permission);
  const sql = `UPDATE roles SET r_permission='${req.body.permission}',r_config='${req.body.config}' WHERE r_id='${req.body.r_id}';`

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
        res.send({error:0,msg:'修改成功',data:data})
    }
  })
})
// 查询
myroute.get('/systempermission/get',(req,res)=>{
  // const page = req.query.page;
  // const pageSize = req.query.pageSize;
  const sql1 =`SELECT * FROM roles LEFT JOIN department ON roles.r_depart= department.id
  WHERE r_name LIKE '${req.query.name}%' and r_permission like '%${req.query.permission}%' order by r_id;
  `;
  const sql2=`select count(*) as total from roles LEFT JOIN department ON roles.r_depart= department.id
  WHERE r_name LIKE '${req.query.name}%' and r_permission like '%${req.query.permission}%';`;
  mySql.query(sql1,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      mySql.query(sql2,(err2,data2)=>{
        if(err2){
          console.log(err2);
          res.send({error:1,msg:err2})
        }else{
          console.log(data2);
          res.send({error:0,msg:'查询权限配置成功',data:data,total:data2[0].total})
        }
      })
    }
  })
})


// 3、用户登录日志
// 渲染表格数据、查询功能
myroute.get('/systemlogin/get',(req,res)=>{
  // console.log(req.query.time[0]);
  // console.log(req.query.time[1]);

  const sql1 = `SELECT * FROM LOGS LEFT JOIN admin ON logs.l_operation = admin.work_id
  LEFT JOIN postadmin ON admin.jobs = postadmin.post_id
  LEFT JOIN department ON postadmin.post_department = department.id WHERE name LIKE '%${req.query.name}%' AND post_name LIKE '%${req.query.postname}%' and l_login>="${req.query.time[0] || '1900-1-1'}" and l_login<="${req.query.time[1] || '2999-1-1'}" order by l_id;`;
  const sql2 = `select count(*) as total from logs LEFT JOIN admin ON logs.l_operation = admin.work_id
  LEFT JOIN postadmin ON admin.jobs = postadmin.post_id
  LEFT JOIN department ON postadmin.post_department = department.id WHERE name LIKE '%${req.query.name}%' AND post_name LIKE '%${req.query.postname}%' and l_login>="${req.query.time[0] || '1900-1-1'}" and l_login<="${req.query.time[1] || '2999-1-1'}";`;
  mySql.query(sql1,(err,data)=>{
    if(err){
      console.log(err);
      res.send({code:300,msg:err})
    }else{
      mySql.query(sql2,(err2,data2)=>{
        if(err2){
          console.log(err2);
          res.send({error:1,msg:err2})
        }else{
          res.send({error:0,msg:'查询登录日志成功',data:data,total:data2[0].total})
        }
      })
    }
  })
})
// 新增登录日志
myroute.post('/systemlogin/add',(req,res)=>{
  const sql =`INSERT INTO LOGS VALUES(NULL,'${req.body.l_account}','${req.body.l_text}','${req.body.l_login}','${req.body.l_caozuo}','${req.body.l_ip}');`;

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      res.send({error:0,msg:'新增登录日志成功',data:data})
    }
  })
})


// 4、用户操作日志
// 渲染表格数据、搜索栏查询功能
myroute.get('/systemoperate/get',(req,res)=>{

  const sql1 = `SELECT * FROM LOGS LEFT JOIN admin ON logs.l_operation = admin.work_id
  LEFT JOIN postadmin ON admin.jobs = postadmin.post_id
  LEFT JOIN department ON postadmin.post_department = department.id WHERE account LIKE '%${req.query.account}%' AND name LIKE '%${req.query.user}%' and l_caozuo>="${req.query.time[0] || '1900-1-1'}" and l_login<="${req.query.time[1] || '2999-1-1'}" order by l_id;`;

  const sql2 = `select count(*) as total from logs LEFT JOIN admin ON logs.l_operation = admin.work_id
  LEFT JOIN postadmin ON admin.jobs = postadmin.post_id
  LEFT JOIN department ON postadmin.post_department = department.id WHERE account LIKE '%${req.query.account}%' AND name LIKE '%${req.query.user}%' and l_caozuo>="${req.query.time[0] || '1900-1-1'}" and l_login<="${req.query.time[1] || '2999-1-1'}";`;
  mySql.query(sql1,(err,data)=>{
    if(err){
      console.log(err);
      res.send({code:300,msg:err})
    }else{
      mySql.query(sql2,(err2,data2)=>{
        if(err2){
          console.log(err2);
          res.send({error:1,msg:err2})
        }else{
          // console.log(data2);
          res.send({error:0,msg:'查询操作日志成功',data:data,total:data2[0].total})
        }
      })
    }
  })
})
// 查看详情
myroute.get('/systemoperate/detail',(req,res)=>{
  // console.log(req.query.l_id);
  const sql = `SELECT * FROM LOGS LEFT JOIN admin ON logs.l_operation = admin.work_id
  LEFT JOIN postadmin ON admin.jobs = postadmin.post_id
  LEFT JOIN department ON postadmin.post_department = department.id WHERE l_id='${req.query.l_id}';`;

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      // console.log(data);
      res.send({error:0,msg:'查询操作日志详情成功',data:data})
    }
  })
})


// 小程序uniapp项目
// 1.1、页面1——渲染数据
myroute.get('/search/get',(req,res)=>{
  const sql = `SELECT * FROM history_record;`;

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      // console.log(data);
      res.send({error:0,msg:'查询关键字成功',data:data})
    }
  })
})
// 1.2、页面1——新增历史记录
myroute.post('/search/add',(req,res)=>{
  const sql = `INSERT INTO history_record VALUES(null,'${req.body.value}');`;

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      // console.log(data);
      res.send({error:0,msg:'新增关键字成功',data:data})
    }
  })
})
// 1.3、页面1——清空所有
myroute.get('/search/del',(req,res)=>{
  const sql = `TRUNCATE TABLE history_record;`;

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      console.log(data);
      res.send({error:0,msg:'清空历史记录成功',data:data})
    }
  })
})

// 2.1、页面2:——渲染数据（搜索结果）
myroute.get('/result/get',(req,res)=>{
  // console.log(req.query.title);
  const sql = `SELECT * FROM notice WHERE notice_title LIKE '%${req.query.title}%'
  `;

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      // console.log(data);
      res.send({error:0,msg:'搜索成功',data:data})
    }
  })
})

// 3.1、页面3——渲染请假类型
myroute.get('/application_type/get',(req,res)=>{
  // console.log(req.query.account);
  const sql = `SELECT * FROM application_type`;

  mySql.query(sql,(err,data)=>{
    if(err){
      console.log(err);
      res.send({error:1,msg:err})
    }else{
      console.log(data);
      res.send({error:0,msg:'渲染请假类型数据成功',data:data})
    }
  })
})
// 3.2、页面3——提交——添加数据
myroute.post('/application/add', (req, res) => {
  
  const sql = `INSERT INTO application VALUES(null,'LS${req.body.num}','${req.body.type}','${req.body.state}','${req.body.audit}','${req.body.starttime}','${req.body.content}','${req.body.endtime}','${req.body.applytime}',null,'${req.body.audit}',null,null,null,null);`;

  const sql2 = `INSERT INTO message VALUES(NULL,'${req.body.name}','${req.body.name}于${req.body.applytime}发起一条请假申请，请您处理','${req.body.applytime}',default,2,'LS${req.body.num}')`;

  mySql.query(sql,(err,data) => {
      if(err){
          console.log(err)
          res.send({err:1,msg:err})
      }else{
          mySql.query(sql2,(err2,data2) => {
              if(err2){
                  console.log(err2)
                  res.send({err:1,msg:err2})
              } else {
                  res.send({error:0,msg:'提交请假申请成功',data:data2})
              }
          })
      }
  })
})

// 4.1、页面4——外勤申请之添加数据
myroute.post('/application_field/add',(req,res)=>{
  // console.log(req.query.detail);
  const sql = `INSERT INTO application VALUES(null,'LS${req.body.num}','4','${req.body.state}','${req.body.audit}','${req.body.starttime}','${req.body.detail}','${req.body.endtime}','${req.body.applytime}',null,'${req.body.audit}',null,null,'${req.body.place}',null);`;

  const sql2 = `INSERT INTO message VALUES(NULL,'${req.body.name}','${req.body.name}于${req.body.applytime}发起一条外勤申请，请您处理','${req.body.applytime}',default,2,'LS${req.body.num}')`;

  mySql.query(sql,(err,data) => {
      if(err){
          console.log(err)
          res.send({err:1,msg:err})
      }else{
          mySql.query(sql2,(err2,data2) => {
              if(err2){
                  console.log(err2)
                  res.send({err:1,msg:err2})
              } else {
                  res.send({error:0,msg:'提交外勤申请成功',data:data2})
              }
          })
      }
  })
})
module.exports = myroute